package com.cari.oa.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import com.cari.oa.domain.MarketProjectEntity;
import com.cari.oa.util.StrUtils;

@Repository
public class MarketProjectDao extends BaseDao<MarketProjectEntity> {
	
   /**
    * 分页查询项目列表
    * @param name	项目名
    * @param province	省份
    * @param page
    * @param size
    * @return
    * guotingting 2013-1-11 下午3:51:51
    */
    @SuppressWarnings("unchecked")
	public List<MarketProjectEntity> getPage(String name, String province, String applyUserCN, String applyUser, Date applyTime, String status, Integer sort, int page, int size) {
    	List<Object> params = new ArrayList<Object>();

        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select p.* from T_MARKETPROJECT p left join (");
        stringBuilder.append("select object_id, max(create_time) as create_time ");
        stringBuilder.append("from T_COMMENT c where c.object_type = 2 group by c.object_id");
        stringBuilder.append(") pc on p.id = pc.object_id left join T_SYS_USER u on u.enname = p.apply_user where 1=1 ");

        if(StrUtils.isNotEmpty(name)) {
            stringBuilder.append("and p.name like ? ");
            params.add("%" + name + "%");
        }
        
        if(StrUtils.isNotEmpty(applyUser)) {
            stringBuilder.append("and p.apply_user = ? ");
            params.add(applyUser);
        }
        
        if(StrUtils.isNotEmpty(applyUserCN)) {
            stringBuilder.append("and u.cnname like ? ");
            params.add("%" + applyUserCN + "%");
        }
        
        if(StrUtils.isNotEmpty(status)) {
            stringBuilder.append("and p.status like ? ");
            params.add("%" + status + "%");
        }

        if(province != null) {
            stringBuilder.append("and p.province in ( " );
            stringBuilder.append(province);
            stringBuilder.append(") ");
        }
        
        if(applyTime != null){
        	stringBuilder.append("and p.apply_time > ? ");
            params.add(applyTime);
        }
        if(sort == 1 )
        	stringBuilder.append("order by pc.create_time desc nulls last");
        else
        	stringBuilder.append("order by p.apply_time desc nulls last");
        
        Query query = getSession().createSQLQuery(stringBuilder.toString()).addEntity(MarketProjectEntity.class);
        for (int i = 0; i < params.size(); i++) {
            Object obj = params.get(i);
            query.setParameter(i, obj);
        }

        query.setFirstResult((page-1)*size).setMaxResults(size);
        return (List<MarketProjectEntity>)query.list();
    }
    
    /**
     * 项目查询记录条数
     * @param name 项目名
     * @param province 省份
     * @return
     * guotingting 2013-1-11 下午3:53:53
     */
    public long getCount(String name, String province, String applyUserCN, String applyUser, Date applyTime, String status) {
    	List<Object> params = new ArrayList<Object>();

        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select count(*) from T_MARKETPROJECT p left join T_SYS_USER u on u.enname = p.apply_user where 1=1 ");
        
        if(StrUtils.isNotEmpty(name)) {
            stringBuilder.append("and p.name like ? ");
            params.add("%" + name + "%");
        }

        if(StrUtils.isNotEmpty(applyUserCN)) {
            stringBuilder.append("and u.cnname like ? ");
            params.add("%" + applyUserCN + "%");
        }
        
        if(province != null) {
            stringBuilder.append("and p.province in ( " );
            stringBuilder.append(province);
            stringBuilder.append(") ");
        }
        
        if(StrUtils.isNotEmpty(applyUser)) {
            stringBuilder.append("and p.apply_user = ? ");
            params.add(applyUser);
        }
        
        if(applyTime != null){
        	stringBuilder.append("and p.apply_time >= ? ");
            params.add(applyTime);
        }
        
        if(StrUtils.isNotEmpty(status)) {
            stringBuilder.append("and p.status like ? ");
            params.add("%" + status + "%");
        }
        
        Query query = getSession().createSQLQuery(stringBuilder.toString());
        for (int i = 0; i < params.size(); i++) {
            Object obj = params.get(i);
            query.setParameter(i, obj);
        }
        Object object = query.uniqueResult();
		
		return Long.parseLong(object.toString());
    }
    
    /**
     * 查询项目
     * @param name	项目名
     * @param province	省份
     * @param page
     * @param size
     * @return
     * guotingting 2013-1-11 下午3:51:51
     */
     @SuppressWarnings("unchecked")
 	public List<MarketProjectEntity> search(String name, String province, String applyUserCN, String applyUser, Date applyTime, String status, Integer sort) {
     	List<Object> params = new ArrayList<Object>();

         StringBuilder stringBuilder = new StringBuilder();
         stringBuilder.append("select p.* from T_MARKETPROJECT p left join (");
         stringBuilder.append("select object_id, max(create_time) as create_time ");
         stringBuilder.append("from T_COMMENT c where c.object_type = 2 group by c.object_id");
         stringBuilder.append(") pc on p.id = pc.object_id left join T_SYS_USER u on u.enname = p.apply_user where 1=1 ");

         if(StrUtils.isNotEmpty(name)) {
             stringBuilder.append("and p.name like ? ");
             params.add("%" + name + "%");
         }
         
         if(StrUtils.isNotEmpty(applyUser)) {
             stringBuilder.append("and p.apply_user = ? ");
             params.add(applyUser);
         }
         
         if(StrUtils.isNotEmpty(applyUserCN)) {
             stringBuilder.append("and u.cnname like ? ");
             params.add("%" + applyUserCN + "%");
         }
         
         if(StrUtils.isNotEmpty(status)) {
             stringBuilder.append("and p.status like ? ");
             params.add("%" + status + "%");
         }

         if(province != null) {
             stringBuilder.append("and p.province in ( " );
             stringBuilder.append(province);
             stringBuilder.append(") ");
         }
         
         if(applyTime != null){
         	stringBuilder.append("and p.apply_time > ? ");
             params.add(applyTime);
         }
         if(sort == 1 )
         	stringBuilder.append("order by pc.create_time desc nulls last");
         else
         	stringBuilder.append("order by p.apply_time desc nulls last");
         
         Query query = getSession().createSQLQuery(stringBuilder.toString()).addEntity(MarketProjectEntity.class);
         for (int i = 0; i < params.size(); i++) {
             Object obj = params.get(i);
             query.setParameter(i, obj);
         }

         return (List<MarketProjectEntity>)query.list();
     }

    /**
    * 根据申请人获取项目
    *@param username
    *@return
    *@author hjx
    *@date 2013-5-7下午2:59:44
     */
	@SuppressWarnings("unchecked")
	public List<MarketProjectEntity> getProjectByApplyUser(String username) {
	     List<MarketProjectEntity> projects = null;
	    try {
			StringBuilder hql = new StringBuilder();
			 hql.append(" select new map(m.id as id,m.status as status,u.cnname as cnName,m.name as name) from  MarketProjectEntity m,SysUserEntity u");
			 hql.append(" where ");
			 hql.append(" m.applyUser = u.enname");
			 if(StrUtils.isNotEmpty(username))
			      hql.append(" and m.applyUser = '"+username+"'");
			 Query query = getSession().createQuery(hql.toString());
			 projects = new ArrayList<MarketProjectEntity>();
			 List<Map<String, String>>  list=  query.list(); 
			 for(Map<String, String> map: list )
			 {
				 MarketProjectEntity m = new MarketProjectEntity();
				 m.setId(map.get("id"));
				 m.setName(map.get("name"));
				 if(username == null)
				     m.setApplyCnname("("+map.get("cnName")+")");
				 if(map.get("status").equals("1"))
				     m.setStatusName("(注销)");
				 projects.add(m);
			 }
		} catch (HibernateException e) {
			e.printStackTrace();
		}
	     return  projects;      
	}
}
